﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ComExcel = Microsoft.Office.Interop.Excel;
using System.Data;
namespace WB.Library
{
    public class FExcel
    {
        public static void ExportTemplate(string templateName, string saveas, DataTable data, string[] columns, int col, int row)
        {
            ComExcel.Application exApp = new ComExcel.Application();
            // Thêm file temp xls
            //COMExcel.Workbook exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
            // Lấy sheet 1.
            //COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];

            // Mo template san co
            string path = Environment.CurrentDirectory + "\\xls\\" + templateName + ".xls";
            ComExcel.Workbook exBook = exApp.Workbooks.Open(path,
                                            0, false, 5, "", "", false, ComExcel.XlPlatform.xlWindows, "",
                                            true, false, 0, true, false, false);
            ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];

            ComExcel.Range range;
            exApp.DisplayAlerts = false;
            try
            {
                for (int r = 0; r < data.Rows.Count; r++)
                {
                    for (int c = 0; c < columns.Length; c++)
                    {
                        range = (ComExcel.Range)exSheet.Cells[row + r, col + c];
                        range.Value2 = "'" + data.Rows[r][columns[c]].ToString();
                        range.WrapText = true;
                    }
                }

                exBook.SaveAs(saveas, ComExcel.XlFileFormat.xlWorkbookNormal,
                    null, null, false, false,
                    ComExcel.XlSaveAsAccessMode.xlExclusive,
                    false, false, false, false, false);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Export không thành công", "Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            exApp.DisplayAlerts = true;
            exApp.Visible = false;
            exApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
        }
    }
}
